package com.meilele.dao.hongdian;

import com.meilele.entity.hongdian.ThiCase;
import com.meilele.entity.hongdian.ThiCaseLable;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface ThiCaseMapper {
    @Delete({
        "delete from thi_case",
        "where case_id = #{caseId,jdbcType=INTEGER}"
    })
    int deleteByPrimaryKey(Integer caseId);

    @Insert({
        "insert into thi_case (case_id, case_big_image, ",
        "case_image, case_desc, ",
        "lable_id, case_company, ",
        "case_people, show_id, ",
        "create_time, case_picture)",
        "values (#{caseId,jdbcType=INTEGER}, #{caseBigImage,jdbcType=VARCHAR}, ",
        "#{caseImage,jdbcType=VARCHAR}, #{caseDesc,jdbcType=VARCHAR}, ",
        "#{lableId,jdbcType=INTEGER}, #{caseCompany,jdbcType=VARCHAR}, ",
        "#{casePeople,jdbcType=INTEGER}, #{showId,jdbcType=INTEGER}, ",
        "#{createTime,jdbcType=TIMESTAMP}, #{casePicture,jdbcType=INTEGER})"
    })
    int insert(ThiCase record);

    int insertSelective(ThiCase record);

    @Insert({"insert into thi_case (case_big_image, " +
            "case_image, case_desc, " +
            "lable_id, case_company, " +
            "case_people, show_id, " +
            "create_time, case_picture)\n" +
            "values ('#{caseBigImage}'," +
            "'#{caseImage}','#{caseDesc}'," +
            "#{lableId},'#{caseCompany}'," +
            "#{casePeople},#{showId}," +
            "'#{createTime}',#{casePicture})\n"
    })
    void saveCase(ThiCase thiCase);

    @Select({
        "select",
        "case_id, case_big_image, case_image, case_desc, lable_id, case_company, case_people, ",
        "show_id, create_time, case_picture",
        "from thi_case",
        "where case_id = #{caseId,jdbcType=INTEGER}"
    })
    @ResultMap("com.meilele.dao.hongdian.ThiCaseMapper.BaseResultMap")
    ThiCase selectByPrimaryKey(Integer caseId);

    /**
     * 查询所有案例（经过排序的）
     * @param firstPage 分页
     * @return
     */
    @Select("SELECT case_id, case_big_image, case_image, case_desc, lable_id, case_company, case_people, show_id, create_time, case_picture FROM thi_case ORDER BY create_time DESC limit #{aa},#{bb}")
    @ResultMap("com.meilele.dao.hongdian.ThiCaseMapper.BaseResultMap")
    List<ThiCase> findAllCase(@Param("aa") int firstPage,@Param("bb") int bb);

    /**
     * 查询所有案例（未经排序的）
     * @param firstPage
     * @param resultPage
     * @return
     */
    @Results(value = {
            @Result(column="case_id" , property="caseId"),
            @Result(column="case_big_image" , property="caseBigImage"),
            @Result(column="case_image" , property="caseImage"),
            @Result(column="case_desc" , property="caseDesc"),
            @Result(column="lable_id" , property="lableId"),
            @Result(column="case_company" , property="caseCompany"),
            @Result(column="case_people" , property="casePeople"),
            @Result(column="show_id" , property="showId"),
            @Result(column="create_time" , property="createTime"),
            @Result(column="case_picture" , property="casePicture") ,
            @Result(column = "lable_id" , property = "caseLableList",
                    javaType = List.class ,
                    many = @Many(select = "com.meilele.dao.hongdian.ThiCaseMapper.findLableManagerByLableId"))
    })
    @Select("SELECT case_id, case_big_image, case_image, case_desc, lable_id, case_company, case_people, show_id, create_time, case_picture FROM thi_case limit #{firstPage},#{resultPage}")
    List<ThiCase> findAllCaseManager(@Param("firstPage") int firstPage,@Param("resultPage") int resultPage);
    @Results(value = {
            @Result(column = "case_lable" , property = "caseLable")
    })
    @Select("SELECT lable_id, case_lable FROM thi_case_lable WHERE lable_id = #{lableId}")
    List<ThiCaseLable> findLableManagerByLableId(int lableId);


    @Results(value = {
            @Result(column="case_id" , property="caseId"),
            @Result(column="case_big_image" , property="caseBigImage"),
            @Result(column="case_image" , property="caseImage"),
            @Result(column="case_desc" , property="caseDesc"),
            @Result(column="lable_id" , property="lableId"),
            @Result(column="case_company" , property="caseCompany"),
            @Result(column="case_people" , property="casePeople"),
            @Result(column="show_id" , property="showId"),
            @Result(column="create_time" , property="createTime"),
            @Result(column="case_picture" , property="casePicture")
    })
    @Select("SELECT case_id, case_desc, case_company, case_picture FROM thi_case WHERE case_company = #{caseCompany}\n")
    List<ThiCase> findCompany(String caseCompany);


    /**
     * 根据人气排序查询出所有的房子，以及其所属的标签
     * @return
     */
    @Results(value = {
            @Result(column="case_id" , property="caseId"),
            @Result(column="case_big_image" , property="caseBigImage"),
            @Result(column="case_image" , property="caseImage"),
            @Result(column="case_desc" , property="caseDesc"),
            @Result(column="lable_id" , property="lableId"),
            @Result(column="case_company" , property="caseCompany"),
            @Result(column="case_people" , property="casePeople"),
            @Result(column="show_id" , property="showId"),
            @Result(column="create_time" , property="createTime"),
            @Result(column="case_picture" , property="casePicture") ,
            @Result(column = "case_id" , property = "caseLableList",
                    javaType = List.class ,
                    many = @Many(select = "com.meilele.dao.hongdian.ThiCaseMapper.findLablesById"))
    })
    @Select({"SELECT case_id, case_big_image, case_image, case_desc, lable_id, case_company, case_people, show_id, create_time, case_picture \n" +
            "FROM thi_case \n" +
            "ORDER BY case_people DESC \n" +
            "limit #{firstPage},#{resultPage}"})
    List<ThiCase> findAllCaseDescPeople(@Param("firstPage") int firstPage,@Param("resultPage") int resultPage);

    //表示数据库中的数据到底有多少条，为分页准备
    @Select("select count(case_id) from thi_case")
    int findCount();


    /**
     * 房子与标签之间多对多的关系
     * @return
     */
    @Results(value = {
            @Result(column="case_id" , property="caseId"),
            @Result(column="case_big_image" , property="caseBigImage"),
            @Result(column="case_image" , property="caseImage"),
            @Result(column="case_desc" , property="caseDesc"),
            @Result(column="lable_id" , property="lableId"),
            @Result(column="case_company" , property="caseCompany"),
            @Result(column="case_people" , property="casePeople"),
            @Result(column="show_id" , property="showId"),
            @Result(column="create_time" , property="createTime"),
            @Result(column="case_picture" , property="casePicture") ,
            @Result(column = "case_id" , property = "caseLableList",
            javaType = List.class ,
                    many = @Many(select = "com.meilele.dao.hongdian.ThiCaseMapper.findLablesById"))
    })
    @Select("SELECT c.case_id, c.case_big_image, c.case_image, c.case_desc, c.lable_id, c.case_company, c.case_people, c.show_id, c.create_time, c.case_picture FROM thi_case c\n" +
            "INNER JOIN thi_lable l\n" +
            "ON c.case_id = l.case_id\n" +
            "INNER JOIN thi_case_lable cl\n" +
            "ON cl.lable_id = l.lable_id\n" +
            "WHERE cl.case_lable = '简约' " +
            "limit #{firstPage},#{resultPage}"
    )
    List<ThiCase> findAllLableCase(@Param("firstPage") int firstPage,@Param("resultPage") int resultPage);

    /**
     * 在查询房子的时候，顺便查出每个房子所属的标签
     * @param caseId 根据每个房子的id不同区查
     * @return
     */
    @Results(value = {
            @Result(column = "case_lable" , property = "caseLable")
    })
    @Select("SELECT cl.lable_id , cl.case_lable FROM thi_case c\n" +
            "INNER JOIN thi_lable l\n" +
            "ON c.case_id = l.case_id\n" +
            "INNER JOIN thi_case_lable cl\n" +
            "ON l.lable_id = cl.lable_id\n" +
            "WHERE c.case_id = #{caseId}")
    List<ThiCaseLable> findLablesById(int caseId);



    int updateByPrimaryKeySelective(ThiCase record);

    @Update({
        "update thi_case",
        "set case_big_image = #{caseBigImage,jdbcType=VARCHAR},",
          "case_image = #{caseImage,jdbcType=VARCHAR},",
          "case_desc = #{caseDesc,jdbcType=VARCHAR},",
          "lable_id = #{lableId,jdbcType=INTEGER},",
          "case_company = #{caseCompany,jdbcType=VARCHAR},",
          "case_people = #{casePeople,jdbcType=INTEGER},",
          "show_id = #{showId,jdbcType=INTEGER},",
          "create_time = #{createTime,jdbcType=TIMESTAMP},",
          "case_picture = #{casePicture,jdbcType=INTEGER}",
        "where case_id = #{caseId,jdbcType=INTEGER}"
    })
    int updateByPrimaryKey(ThiCase record);


    List<ThiCase> findAllLables();

    @Insert({
            "insert into thi_case (case_id, case_big_image, ",
            "case_image, case_desc, ",
            "lable_id, case_company, ",
            "case_people, show_id, ",
            "create_time, case_picture)",
            "values (#{caseId,jdbcType=INTEGER}, #{caseBigImage,jdbcType=VARCHAR}, ",
            "#{caseImage,jdbcType=VARCHAR}, #{caseDesc,jdbcType=VARCHAR}, ",
            "#{lableId,jdbcType=INTEGER}, #{caseCompany,jdbcType=VARCHAR}, ",
            "#{casePeople,jdbcType=INTEGER}, #{showId,jdbcType=INTEGER}, ",
            "#{createTime,jdbcType=TIMESTAMP}, #{casePicture,jdbcType=INTEGER})"
    })
    void save(ThiCase thiCase);

    /**
     * 做修改操作的时候，根据id查询出需要修改的案例
     * @param caseId
     * @return
     */
    @Results(value = {
            @Result(column="case_id" , property="caseId"),
            @Result(column="case_big_image" , property="caseBigImage"),
            @Result(column="case_image" , property="caseImage"),
            @Result(column="case_desc" , property="caseDesc"),
            @Result(column="lable_id" , property="lableId"),
            @Result(column="case_company" , property="caseCompany"),
            @Result(column="case_people" , property="casePeople"),
            @Result(column="show_id" , property="showId"),
            @Result(column="create_time" , property="createTime"),
            @Result(column="case_picture" , property="casePicture")
    })
    @Select("SELECT case_id, case_big_image, case_image, case_desc, lable_id, case_company, case_people, show_id, create_time, case_picture " +
            "FROM thi_case WHERE case_id = #{caseId}")
    ThiCase findCaseById(int caseId);

    @Delete("DELETE FROM thi_case WHERE case_id = #{caseId}")
    int deleteCase(int caseId);

    @Update("UPDATE thi_case \n" +
            "SET case_id = #{caseId}, case_big_image = #{caseBigImage}, case_image = #{caseImage}, \n" +
            "case_desc = #{caseDesc}, lable_id = #{lableId}, case_company = #{caseCompany}, \n" +
            "case_people = #{casePeople}, show_id = #{showId}, create_time = #{createTime}, \n" +
            "case_picture = #{casePicture} WHERE case_id = #{caseId}")
    int updateCase(ThiCase thiCase);
}